#coding='utf-8'
from requests import Request, Session
import time
from imp import reload
from urllib import request as urllib2
from lxml import etree
import pymysql
import json
import sys
reload(sys)
import requests
#遍历ID补充信息+数据
def get_one_page(url):
    response = requests.get(url)
    response.encoding = "utf-8"
    if response.status_code == 200:
        return response.text
    return None

def main():
    db = pymysql.connect(host='localhost', user='root', password='', port=3306, db='nbaData', charset='utf8')
    cursor = db.cursor()
    sql = 'select id from nbaPlayer where position = ""  '
    cursor.execute(sql)    # 拿到ID遍历网页拿数据
    results = cursor.fetchall()
    insert_sql = 'INSERT INTO playerSeasonSvg(playerId,season, team, game_count, start_count, game_time,fg_percent,fg,fg_count,three_percent,threep,threepa,ftper,ft,fta,trb,orb,drb,ast,stl,blk,tov,pf,pts,w,l)' + \
                 ' values(%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s,%s)'
    for (row,) in results:
        url = 'http://www.stat-nba.com/player/' + str(row) + '.html'
        playerId = row
        text = get_one_page(url)
        dom = etree.HTML(text)
        if dom is None:
            return
        node_list = dom.xpath('//table[@id="stat_box_avg"]/tbody/tr[@class="sort"]')   #遍历节点拿到平均数据
        salary = dom.xpath('//div[text() = "当前薪金:"]/../text()')
        position = dom.xpath('//div[text() = "位　　置:"]/../text()')
        if len(position) == 0:
            position = None
        if len(salary) == 0:
            salary = None
        insert_playerinfo_sql = 'update nbaplayer set salary =%s, position = %s where id = %s '
        cursor.execute(insert_playerinfo_sql, (salary,position, row))
        db.commit()
        for node in node_list:
            season = node.xpath('./td[2]/a/text()')
            team = node.xpath('./td[3]/a/text()')
            game_count = node.xpath('./td[4]/text()')
            start_count = node.xpath('./td[5]/text()')
            game_time = node.xpath('./td[6]/text()')
            fg_percent = node.xpath('./td[7]/text()')
            fg = node.xpath('./td[8]/text()')
            fg_count = node.xpath('./td[9]/text()')
            three_percent = node.xpath('./td[10]/text()')
            threep = node.xpath('./td[11]/text()')
            threepa = node.xpath('./td[12]/text()')
            ftper = node.xpath('./td[13]/text()')
            ft = node.xpath('./td[14]/text()')
            fta = node.xpath('./td[15]/text()')
            trb = node.xpath('./td[16]/text()')
            orb = node.xpath('./td[17]/text()')
            drb = node.xpath('./td[18]/text()')
            ast = node.xpath('./td[19]/text()')
            stl = node.xpath('./td[20]/text()')
            blk = node.xpath('./td[21]/text()')
            tov = node.xpath('./td[22]/text()')
            pf = node.xpath('./td[23]/text()')
            pts = node.xpath('./td[24]/text()')
            w = node.xpath('./td[25]/text()')
            if w:
                l = node.xpath('./td[26]/text()')
                cursor.execute(insert_sql, (
                playerId, season, team, game_count, start_count, game_time, fg_percent, fg, fg_count, three_percent,
                threep, threepa, ftper, ft, fta, trb, orb, drb, ast, stl, blk, tov, pf, pts, w, l))
                db.commit()
            else:
                continue
        time.sleep(1)

if __name__ == '__main__':
    main()